load 'rds_api.rb'
load 'wechat_api.rb'
load 'tutem_utils.rb'

@rds = @rds_tt

def genUserProfilesIn start_time, end_time, uid_select = "select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= _START_TIME and pay_time < _END_TIME", channel_type = 0 


    channel_select = " and u.spread_uid in (select uid from tutem_mall.eb_user where promoter_type = #{channel_type}) "
    channel_select = " " if channel_type == 0
    sql = "select u.uid, u.promoter_type as utype, u.real_name, u.spread_uid as cid,  p.real_name as cname, p.promoter_type as ctype, p.rep_id, p.rep_code, u.mark, u.goods_list, u.last_purchased from tutem_mall.eb_user u left join tutem_mall.eb_user p on (u.spread_uid = p.uid) where u.uid in ( #{uid_select} ) #{channel_select} order by p.rep_code;"

    sql = sql.gsub('_START_TIME',start_time.to_s); #当uid_select写明时此句不再工作
    sql = sql.gsub('_END_TIME',end_time.to_s);

    recs = {}
    (queryRds(sql)).each do |i|

        uid = i['uid']
        cname = i['cname']
        cname = cname[0].to_s + '**'

        ifou = true #ifou is_first_order_user
        fsql = "select id from tutem_mall.eb_store_order where uid = #{uid} and paid = 1 and pay_time < #{start_time}"
        (queryRds(fsql)).each { |f| ifou = false; break }

        rec = {
            'uid'=>i['uid'],'uname'=>i['real_name'], 'utype'=>i['utype'],
            'cid'=>i['cid'],'cname'=>cname, 'ctype'=>i['ctype'], 'ifou'=>ifou,
            'rid'=>i['rep_id'],'rcode'=>i['rep_code'],
            'diagnose'=>i['mark'],'last_call'=>'--','calls'=>'',
            'last_buy'=>i['last_purchased'],
            #tips: 管理提示 #url: 可以独立访问的用户档案摘要外链
            'tips'=>'', 'url'=>'',
            #ordered: 购买历史 umark: 用户评价 #followups：随访报告
            'ordered'=>i['goods_list'], 'umarks'=>'','followups'=>''
        }

        psql = "select p.title, p.memo, p.add_time from tutem_mall.eb_user_profile p where p.uid = #{uid}"

        memos = ''
        latest_time = 0
        (queryRds(psql)).each do |p|
            memos += "#{p['title']} #{p['memo']}\n"
            latest_time = p['add_time'] if p['add_time'] > latest_time
        end

        rec.store 'last_call', Time.at(latest_time).strftime('%Y-%m-%d') if latest_time != 0
        rec.store 'calls', memos

        tips = ifou ? '^ ' : ' '
        tips +=  i['mark'].to_s.empty? ? 'x ' : ' '
        tips +=  latest_time == 0 ? '! ' : ' '
        tips += i['utype'] >= 1 ? "T#{i['utype']} " : ' '
        rec.store 'tips', tips

        url = "http://oms2.totembio.cn/users/UP-#{uid}.txt"
        rec.store 'url', url
        alter = i['mark'].to_s.empty? ? '代表未提供诊断信息' : '请进入管理后台查看'
        rec.store 'diagnose', alter

        txt =  OS.windows? ? ".\\nginx\\html\\users\\UP-#{uid}.txt" : "./nginx/html/users/UP-#{uid}.txt"
        profile = "# 用户档案简报\n"
        profile += "## 【用户】 ##{uid} #{rec['uname']} #{rec['utype']}\n"
        profile += "## 【客户】 ##{rec['cid']} #{rec['cname']} #{rec['ctype']}\n"
        profile += "## 【代表】 ##{rec['rid']} #{rec['rcode']}\n"
        profile += "\n【诊断】#{rec['diagnose']}\n"
        rec.each do |k, v|
            next if k == 'uid' || k == 'cid' || k == 'rid' || k == 'uname' || k == 'cname' || k == 'rcode' || k == 'diagnose' || k == 'ifou'
            profile += "【#{k}】"
            profile += "#{v}\n"
        end
        File.open(txt,"w+:UTF-8") { |f| f.write profile }

        recs.store uid, rec

    end

    return recs
    ap recs

end

def genUserProfileReport recs, period_str, subtitle

    title = "订单跟进简报 - #{subtitle}"
    summary = "## #{title}\n"
    summary += "### [#{period_str})\n"

    summary += "\n"
    summary += "CID REP UID ATT\n"
    summary += "––––––––––––––\n"

    recs.each do |uid, info|

        url = info['url']
        uid_info = sprintf('%05d', info['uid'])
        summary += '[' + sprintf('%05d', info['cid']) + ']'
        summary += ' ' + sprintf('%s',  info['rcode'])
        summary += " [#{uid_info}](#{url})"
        #lpg = (info['last_buy'].split(' '))[1].to_s
        #summary += " #{lpg}"

        tips = info['tips']
        att = "<font color='green'>#{tips}</font>" if tips.include? '^'
        att = "<font color='comment'>#{tips}</font>" if tips.include? 'x'
        att = "<font color='warning'>#{tips}</font>" if tips.include? '!'
        summary += " #{att}"
        summary += "\n"
    end
    summary += "\n\n"

    summary ++ "^ 首单用户 T 专业会员\n"
    summary += "x 诊断信息 ! 暂未回访\n"
    summary += "点击UID可查看用户档案\n"

    summary += "\n\n"

    path = OS.windows? ? ".\\nginx\\html\\users\\" : './nginx/html/users/'
    fn = "URPR-#{period_str}.xls" #user-report-per-rep
    dump_hash_to_excel recs, path+fn, false

    summary += "> #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
    return summary

end


#days: 指运行日期零时往前多少天内
#send: 是否推送企业微信消息
#on_timer < 0 意味着随时可以运行且不考虑已有数据，如果大于等于零则该小时可运行一次；当设置定时后，如果已经有数据文件也跳过运行
def reportPastDaysClinicalUserProfiles days = 1, send = false, on_timer = -1, title = '诊疗昨日成交', end_date = Date.today.to_s

    end_time = Time.parse(end_date + ' 00:00:00').to_i
    start_time = end_time - days * 60 * 60 * 24

    sday = Time.at(start_time).strftime('%Y-%m-%d')
    eday = Time.at(end_time).strftime('%Y-%m-%d')
    period_str = "#{sday}~#{eday}"

    dir = OS.windows? ? ".\\nginx\\html\\users\\" : './nginx/html/users/'
    fn = "CUPR-#{period_str}.xls" #clinical user profile report per rep
    fp = dir + fn #user-report-per-rep

    if ( !(FileTest::exist? fp) && Time.now.hour >= on_timer ) || on_timer < 0

        uid_select = "select uid from tutem_mall.eb_store_order where paid = 1 and pay_time >= #{start_time} and pay_time < #{end_time}"
        channel_type = 1

        recs = genUserProfilesIn start_time, end_time, uid_select, channel_type

        #产生markdown报告
        report = genUserProfileReport recs, period_str, title

        #产生excel数据,并作为标记文件
        dump_hash_to_excel recs, fp, false
=begin
        download = "## 订单跟进简报-#{title}\n"
        download += "## [#{sday},#{eday})\n"
        download += "点击>>[下载完整数据](http://oms2.totembio.cn/users/#{fn})<<\n"
        download += "> #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"
=end

        send_message_to_bot 'WWBOT_TT', report, ['@all'] if send
        puts report

    end

end

def reportUnshippedOrders title
    summary = "广州仓未发货订单 #{title}\n"
    summary += "OID USER TAGS\n"
    summary += "––––––––––––––\n"

    sql = "select id, order_id, uid, real_name, user_address, from_unixtime(pay_time)  as paid_at, tags from tutem_mall.eb_store_order where id in (select oid from tutem_mall.eb_pickup_book where temp_id != 5) and status = 0 and refund_status = 0 and delivery_id is null and refund_price = 0;"
    counter = 0
    (queryRds(sql)).each do |q|
        counter += 1
        summary += sprintf('%5d', q['id'])
        summary += " #{q['real_name']} #{q['tags']}\n"
    end

    summary += "\n"
    summary += "> #{Time.now.strftime('%Y-%m-%d %H:%M:%S')}"

    summary = '' if counter == 0
    return summary
end

def alertUnshippedOrders hhmm = '17:30:00', always = false, prefix='AUSO', briefing = false

    date = Time.now.strftime('%Y%m%d')
    dir = OS.windows? ? ".\\nginx\\html\\users\\" : './nginx/html/users/'
    fn = "#{prefix}-#{date}.txt" #clinical user profile report per rep
    fp = dir + fn #user-report-per-rep

    run_time = Time.parse(Time.now.strftime('%Y-%m-%d') + ' ' + hhmm)
    if ( !(FileTest::exist? fp) && Time.now >= run_time ) || always == true
        report = reportUnshippedOrders hhmm
        if report == ''
            report = '诸事大吉！广州仓订单全部发货完成'
        else
            report = "#广州仓还有订单没有发货" if briefing == true
        end
        #puts report
        send_message_to_bot 'WWBOT_TT', report, ['@all']
        File.open(fp,"w+:UTF-8") { |f| f.write report }
    end

end

#reportPastDaysClinicalUserProfiles  1, false, -1, '昨日'
#reportPastDaysClinicalUserProfiles 30, false, -1, '最近30天'# if Date.today.sunday?
